﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Collections.ObjectModel;
using System.Security.Cryptography;
using System.IO;

namespace Eassets.Data
{
    public class CommonData
    {

        private SqlConnection conn;
        /// <summary>
        /// "static" Can be accessed without making instance
        /// </summary>
        public SqlConnection Connection
        {
            get
            {
                return conn;
            }
        }

        /// <summary>
        /// Prepare the database connection
        /// </summary>
        public void ReadyConnection()
        {
            conn = new SqlConnection();
            conn.ConnectionString = ConfigurationManager.ConnectionStrings["BasicLayeredConnection"].ToString();
            if (conn.State != ConnectionState.Open)
            {
                conn.Open();
            }
        }

        public long ExecuteNonQueryAndReturnIdentityValue(SqlCommand command)
        {
            try
            {
                ReadyConnection();
                command.Connection = conn;
                //return (long)command.ExecuteScalar();
                long test = Convert.ToInt64(command.ExecuteScalar());
                return test;
            }
            catch (Exception exception)
            {
                return -1;
            }
            finally
            {
                command.Dispose();
                if (conn.State == ConnectionState.Open) conn.Close();
                conn.Dispose();
            }
        }


        /// <summary>
        /// To execute non query data.
        /// </summary>
        /// <param name="command"></param>
        /// <returns></returns>
        public int ExecuteNonQuery(SqlCommand command)
        {
            int result;
            try
            {
                ReadyConnection();
                command.Connection = conn;
                result = command.ExecuteNonQuery();
                return result;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                command.Dispose();
                if (conn.State == ConnectionState.Open) conn.Close();
                conn.Dispose();
            }

        }

        /// <summary>
        /// To get tabular data.
        /// </summary>
        /// <param name="command"></param>
        /// <returns></returns>
        public SqlDataReader ExecuteReader(SqlCommand command)
        {
            try
            {
                ReadyConnection();
                command.Connection = conn;
                return command.ExecuteReader();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public object ExecuteScalar(SqlCommand command)
        {
            try
            {
                ReadyConnection();
                command.Connection = conn;

                object value = command.ExecuteScalar();
                if (value is DBNull)
                {
                    return default(decimal);
                }
                else
                {
                    return value;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable GetTable(string tableName)
        {
            DataTable dataTable = new DataTable();

            SqlCommand command = new SqlCommand("GetTable");
            command.Parameters.AddWithValue("@TableName", tableName);
            command.CommandType = CommandType.StoredProcedure;

            try
            {
                ReadyConnection();
                command.Connection = conn;
                dataTable.Load(command.ExecuteReader());

                return dataTable;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public int RunTransaction(Collection<SqlCommand> sqlCommands)
        {
            ReadyConnection();
            SqlTransaction sqlTransaction = conn.BeginTransaction();

            try
            {
                foreach (SqlCommand sqlCommand in sqlCommands)
                {
                    sqlCommand.Connection = conn;
                    sqlCommand.Transaction = sqlTransaction;
                    sqlCommand.ExecuteNonQuery();
                }
                sqlTransaction.Commit();
                return 1;
            }
            catch (Exception exception)
            {
                sqlTransaction.Rollback();
                return 0;
            }
            finally
            {
                if (conn.State == ConnectionState.Open) conn.Close();
                conn.Dispose();
            }
        }
    }

    public class CryptographyeAsset
    {
        private string encryptionKey = "@!&%#@?,";

        public String Encrypt(string text)
        {
            byte[] iv = { 0x12, 0x34, 0x56, 0x78, 0x90, 0xab, 0xcd, 0xef };

            try
            {
                byte[] bykey = System.Text.Encoding.UTF8.GetBytes(encryptionKey);
                byte[] inputByteArray = System.Text.Encoding.UTF8.GetBytes(text);
                DESCryptoServiceProvider des = new DESCryptoServiceProvider();
                MemoryStream ms = new MemoryStream();
                CryptoStream cs = new CryptoStream(ms, des.CreateEncryptor(bykey, iv), CryptoStreamMode.Write);
                cs.Write(inputByteArray, 0, inputByteArray.Length);
                cs.FlushFinalBlock();
                return Convert.ToBase64String(ms.ToArray());
            }
            catch (Exception ex)
            {
                return ex.Message;
            }
        }

        public String Decrypt(string text)
        {
            byte[] iv = { 0x12, 0x34, 0x56, 0x78, 0x90, 0xab, 0xcd, 0xef };
            byte[] inputByteArray = new byte[text.Length];

            try
            {
                byte[] byKey = System.Text.Encoding.UTF8.GetBytes(encryptionKey);
                DESCryptoServiceProvider des = new DESCryptoServiceProvider();
                inputByteArray = Convert.FromBase64String(text);
                MemoryStream ms = new MemoryStream();
                CryptoStream cs = new CryptoStream(ms, des.CreateDecryptor(byKey, iv), CryptoStreamMode.Write);
                cs.Write(inputByteArray, 0, inputByteArray.Length);
                cs.FlushFinalBlock();
                System.Text.Encoding encoding = System.Text.Encoding.UTF8;
                return encoding.GetString(ms.ToArray());
            }
            catch (Exception exception)
            {
                return exception.Message;
            }
        }
    }
}
